from pymongo import MongoClient
from bson import ObjectId
import datetime
import pandas as pd

# 连接管理数据库
host = '52.82.31.133'
port = 29017
username = 'ai_user'
password = 'finfoai123'
database = 'ai'


#数据中心 插入多条数据
def insert_forecast_mongdb_hourly(source_id,forecast_result):
    mong_table = 'ai_heat_index_forecast_result_c'

    data_start =forecast_result[0][0]
    data_end = forecast_result[-1][0]

    # 连接数据库

    # 连接数据库
    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')

    mydb = client[database]
    workTable = mydb[mong_table]

    # 清空历史数据
    workTable.delete_many({"heating_source_id":ObjectId(source_id),
                           "time": {"$lte": data_end},
                           "time": {"$gte": data_start}
                           }
                          )

    print(mong_table,'insert MongDB start')

    partData=[]
    i=0
    for one in forecast_result:
        # 调试
        # if dataId >10:
        #     break
        i=i+1


        data = {'time': one[0], 'heating_source_id':  ObjectId( one[1]), 'heating_value': one[2]}

        # print(data)
        partData.append(data)
        # if int(dataRow['data_id'])==2086563:
        #     print()
        # 每1000条数据插入一次

        if len(partData) == 1000 or i == len(forecast_result):
            try:
                print('%s insert %s data to mongdb' % (i,len(partData)))
                result = workTable.insert_many(partData)
                print('result:', result)
                partData = []
            except Exception as e:
                print('-------insert_mongdb_many_hourly  have error')
                print(e)

    print('insert MongDB end')


#数据中心 插入多条数据
def insert_ai_station_cluster(company_id,company_code,forecast_result,workHour):
    mong_table = 'ai_station_cluster'

    # 连接数据库
    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')

    mydb = client[database]
    workTable = mydb[mong_table]

    # 清空历史数据
    workTable.delete_many({
                           "time": {"$lte": workHour},
                           "time": {"$gte": workHour}
                           }
                          )

    print(mong_table,'insert MongDB start')

    partData=[]
    i=0
    for index,one in forecast_result.iterrows():
        # 调试
        # if dataId >10:
        #     break
        i=i+1


        data = {
            'company_id':company_id,
            'company_code':company_code,
            'egw':round(float(one[0]),2),
            'ehw': round(float(one[1]),2),
            'ewc': round(float(one[2]),2),
            'cluster':one[3],
            'time': workHour,
            'station_name':one[5]
            }

        # print(data)
        partData.append(data)
        # if int(dataRow['data_id'])==2086563:
        #     print()
        # 每1000条数据插入一次

        if len(partData) == 1000 or i == len(forecast_result):
            try:
                print('%s insert %s data to mongdb' % (i,len(partData)))
                result = workTable.insert_many(partData)
                print('result:', result)
                partData = []
            except Exception as e:
                print('-------insert_mongdb_many_hourly  have error')
                print(e)

    print('insert MongDB end')




#数据中心 插入多条数据 含 热量，流量数据
def insert_ai_station_cluster_hour(company_id,company_code,forecast_result,workHour):
    mong_table = 'ai_station_cluster_hour'

    # 连接数据库
    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')

    mydb = client[database]
    workTable = mydb[mong_table]

    # 清空历史数据
    # workTable.delete_many({
    #                        "time": {"$lte": workHour},
    #                        "time": {"$gte": workHour}
    #                        }
    #                       )

    workTable.delete_many({
                           "time": workHour
                           }
                          )

    print(mong_table,'insert MongDB start')

    partData=[]
    i=0
    for index,one in forecast_result.iterrows():
        # 调试
        # if dataId >10:
        #     break
        i=i+1


        data = {
            'company_id':company_id,
            'company_code':company_code,
            'ygw':round(float(one['ygw']),2),
            'yhw': round(float(one['yhw']), 2),

            'egw':round(float(one['egw']),2),
            'ehw': round(float(one['ehw']),2),
            'ewc': round(float(one['ewc']),2),
            'xsrh': round(float(one['xsrh']),2),
            'xsll': round(float(one['xsll']), 2),
            'cluster':one['cluster'],
            'time': workHour,
            'station_name':one['station_name']
            }

        # print(data)
        partData.append(data)
        # if int(dataRow['data_id'])==2086563:
        #     print()
        # 每1000条数据插入一次

        if len(partData) == 1000 or i == len(forecast_result):
            try:
                print('%s insert %s data to mongdb' % (i,len(partData)))
                result = workTable.insert_many(partData)
                print('result:', result)
                partData = []
            except Exception as e:
                print('-------insert_mongdb_many_hourly  have error')
                print(e)

    print('insert MongDB end')



#数据中心 插入多条数据
def insert_ai_station_cluster_detail(cluster_detail,workHour):
    mong_table = 'ai_station_cluster_detail'

    # 连接数据库
    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')

    mydb = client[database]
    workTable = mydb[mong_table]

    record=workTable.find().count()

    if record>0:
        # 清空历史数据
        workTable.delete_many({
                               "time": workHour
                               }
                              )

    print(mong_table,'insert MongDB start')



    partData=[]
    i=0


    for index,one in cluster_detail.iterrows():
        # 调试
        # if dataId >10:
        #     break
        i=i+1

        data = {
            'time': workHour,
            'cluster':index,
            'yhw':round(float(one['yhw']),2),

            'egw': round(float(one['egw']), 2),
            'ehw': round(float(one['ehw']), 2),
            'ewc': round(float(one['ewc']), 2),

            'xsrh': round(float(one['xsrh']), 2),
            'xsll': round(float(one['xsll']), 2),
            'count': int(one['count'])


            }

        # print(data)
        partData.append(data)

        # 每1000条数据插入一次

        if len(partData) == 1000 or i == cluster_detail.shape[0]:
            try:
                print('%s insert %s data to mongdb' % (i,len(partData)))
                result = workTable.insert_many(partData)
                print('result:', result)
                partData = []
            except Exception as e:
                print('-------  have error')
                print(e)

    print('insert MongDB end')



# 取mongdb数据
def get_heat_from_mongdb(source_id,in_time):

    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')
    mydb = client[database]
    db_heat = mydb["ai_heat_index_c"]
    condition =  {"heating_source_id":ObjectId(source_id),
                    "start": {"$lt": in_time},
                  "end": {"$gte": in_time}
                  }

    d_o = db_heat.find(condition)
    heat=list(d_o)[0]

    client.close()

    return heat




# 取mongdb数据 一个cluster的数据
def get_data_of_cluster(cluster,start,end):

    start_time=datetime.datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
    end_time = datetime.datetime.strptime(end, "%Y-%m-%d %H:%M:%S")

    client = MongoClient(host, port)
    client.db = client[database]
    client.db.authenticate(username, password, mechanism='SCRAM-SHA-1')
    mydb = client[database]
    db_heat = mydb["ai_station_cluster_hour"]
    condition =  {"cluster":cluster,
                    "start": {"$lte": start_time},
                  "end": {"$gte": end_time}
                  }

    condition = {"cluster": cluster
                 }

    d_o = db_heat.find(condition)
    l_data=[]
    for record in d_o:
        l_data.append(record)

    client.close()

    data = pd.DataFrame(l_data)

    client.close()


    return data
